﻿using DataAccess.Exceptions;
using Model;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess
{
    public class SubjectToTeacherDataAccess
    {
        public static string ConnString = Conexion.DataSource;
    
        public static SubjectToTeacher newSubjectToTeacher(long ciTeacher, long codSubject, string gestion)
        {
            SqlConnection connection = null;
            try
            {    

                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "INSERT INTO [DocenteDictaMateria] (ciDocente, codMateria, gestion) VALUES (@V_ciDocente, @V_codMateria, @V_gestion)";                
                cmd.Parameters.AddWithValue("V_gestion", gestion);
                cmd.Parameters.AddWithValue("V_ciDocente", ciTeacher);
                cmd.Parameters.AddWithValue("V_codMateria", codSubject);
                connection.Open();

                cmd.ExecuteNonQuery();
                SubjectToTeacher newSTT = getSubjectToTeacherByciTeacherAndcodSubjectAndGestion(ciTeacher, codSubject, gestion);
                return (newSTT);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al crear docente dicta materia", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static SubjectToTeacher modifySubjectToTeacher(long code, long ciTeacher, long codSubject, string gestion)
        {
            SqlConnection connection = null;
            try
            {

                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE [DocenteDictaMateria] SET ciDocente=@V_ciDocente, codMateria=@V_codMateria, gestion=@V_gestion WHERE cod=@code";
                cmd.Parameters.AddWithValue("V_gestion", gestion);
                cmd.Parameters.AddWithValue("V_ciDocente", ciTeacher);
                cmd.Parameters.AddWithValue("V_codMateria", codSubject);
                cmd.Parameters.AddWithValue("code", code);
                connection.Open();

                cmd.ExecuteNonQuery();
                SubjectToTeacher newSTT = getSubjectToTeacherByciTeacherAndcodSubjectAndGestion(ciTeacher, codSubject, gestion);
                return (newSTT);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al modificar docente dicta materia", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static SubjectToTeacher getSubjectToTeacherByciTeacherAndcodSubjectAndGestion(long ciTeacher, long codSubject, string gestion)
        {
            SqlConnection connection = null;
            SubjectToTeacher result = null;
            try
            {

                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [DocenteDictaMateria] WHERE ciDocente=@V_ciDocente AND codMateria=@V_codMateria AND gestion=@V_gestion";
                cmd.Parameters.AddWithValue("V_gestion", gestion);
                cmd.Parameters.AddWithValue("V_ciDocente", ciTeacher);
                cmd.Parameters.AddWithValue("V_codMateria", codSubject);
                connection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    result = new SubjectToTeacher();
                    result.code = Convert.ToInt32(reader["cod"].ToString());
                    result.ciTeacher = Convert.ToInt32(reader["ciDocente"].ToString());
                    result.codSubject = Convert.ToInt32(reader["codMateria"].ToString());
                    result.gestion = reader["gestion"].ToString();
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al acceder al dato", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static SubjectToTeacher getSubjectToTeacherByCode(long code)
        {
            SqlConnection connection = null;
            SubjectToTeacher result = null;
            try
            {

                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [DocenteDictaMateria] WHERE cod=@V_cod";                
                cmd.Parameters.AddWithValue("V_cod", code);
                connection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    result = new SubjectToTeacher();
                    result.code = Convert.ToInt32(reader["cod"].ToString());
                    result.ciTeacher = Convert.ToInt32(reader["ciDocente"].ToString());
                    result.codSubject = Convert.ToInt32(reader["codMateria"].ToString());
                    result.gestion = reader["gestion"].ToString();
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al acceder al dato", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Collection<SubjectToTeacher> getAllSubjectToTeacher()
        {
            SqlConnection connection = null;
            Collection<SubjectToTeacher> result = null;
            try
            {

                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [DocenteDictaMateria]";
                
                connection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                result = new Collection<SubjectToTeacher>();
                while (reader.Read())
                {
                    SubjectToTeacher item = new SubjectToTeacher();
                    item.code = Convert.ToInt32(reader["cod"].ToString());
                    item.ciTeacher = Convert.ToInt32(reader["ciDocente"].ToString());
                    item.codSubject = Convert.ToInt32(reader["codMateria"].ToString());
                    item.gestion = reader["gestion"].ToString();

                    result.Add(item);
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al acceder a los datos", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Collection<SubjectToTeacher> getAllSubjectToTeacherByCodeSubject(string filterCodeSubject)
        {
            SqlConnection connection = null;
            Collection<SubjectToTeacher> result = null;
            try
            {

                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [DocenteDictaMateria] WHERE codMateria LIKE @V_codMateria";
                cmd.Parameters.AddWithValue("V_codMateria", filterCodeSubject + "%");

                connection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                result = new Collection<SubjectToTeacher>();
                while (reader.Read())
                {
                    SubjectToTeacher item = new SubjectToTeacher();
                    item.code = Convert.ToInt32(reader["cod"].ToString());
                    item.ciTeacher = Convert.ToInt32(reader["ciDocente"].ToString());
                    item.codSubject = Convert.ToInt32(reader["codMateria"].ToString());
                    item.gestion = reader["gestion"].ToString();

                    result.Add(item);
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al acceder a los datos", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<SubjectToTeacher> getAllSubjectToTeacherByCodeTeacher(string filterCITeacher)
        {
            SqlConnection connection = null;
            Collection<SubjectToTeacher> result = null;
            try
            {

                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [DocenteDictaMateria] WHERE ciDocente LIKE @V_ciDocente";
                cmd.Parameters.AddWithValue("V_ciDocente", filterCITeacher + "%");

                connection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                result = new Collection<SubjectToTeacher>();
                while (reader.Read())
                {
                    SubjectToTeacher item = new SubjectToTeacher();
                    item.code = Convert.ToInt32(reader["cod"].ToString());
                    item.ciTeacher = Convert.ToInt32(reader["ciDocente"].ToString());
                    item.codSubject = Convert.ToInt32(reader["codMateria"].ToString());
                    item.gestion = reader["gestion"].ToString();

                    result.Add(item);
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al acceder a los datos", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<SubjectToTeacher> getAllSubjectToTeacherByNameSubject(string filterNameSubject)
        {
            SqlConnection connection = null;
            Collection<SubjectToTeacher> result = null;
            try
            {
                Collection<Subject> searchName = DataAccess.SubjectDataAccess.getSubjectByName(filterNameSubject, true);
                if (searchName != null)
                {
                    for (int ptr = 0; ptr < searchName.Count; ptr++)
                    {
                        connection = new SqlConnection(ConnString);
                        SqlCommand cmd = connection.CreateCommand();
                        cmd.CommandText = "SELECT * FROM [DocenteDictaMateria] WHERE codMateria LIKE @V_codMateria";
                        cmd.Parameters.AddWithValue("V_codMateria", searchName[ptr].code);

                        connection.Open();

                        SqlDataReader reader = cmd.ExecuteReader();
                        result = new Collection<SubjectToTeacher>();
                        while (reader.Read())
                        {
                            SubjectToTeacher item = new SubjectToTeacher();
                            item.code = Convert.ToInt32(reader["cod"].ToString());
                            item.ciTeacher = Convert.ToInt32(reader["ciDocente"].ToString());
                            item.codSubject = Convert.ToInt32(reader["codMateria"].ToString());
                            item.gestion = reader["gestion"].ToString();

                            result.Add(item);
                        }
                    }
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al acceder a los datos", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<SubjectToTeacher> getAllSubjectToTeacherByCITeacher(string filterCITeacher)
        {
            SqlConnection connection = null;
            Collection<SubjectToTeacher> result = null;
            try
            {
                Collection<Teacher> searchName = DataAccess.TeacherDataAccess.getTeacherLikeByCI(filterCITeacher);
                if (searchName != null)
                {
                    for (int ptr = 0; ptr < searchName.Count; ptr++)
                    {
                        connection = new SqlConnection(ConnString);
                        SqlCommand cmd = connection.CreateCommand();
                        cmd.CommandText = "SELECT * FROM [DocenteDictaMateria] WHERE ciDocente LIKE @V_ciDocente";
                        cmd.Parameters.AddWithValue("V_ciDocente", searchName[ptr].CI);

                        connection.Open();

                        SqlDataReader reader = cmd.ExecuteReader();
                        result = new Collection<SubjectToTeacher>();
                        while (reader.Read())
                        {
                            SubjectToTeacher item = new SubjectToTeacher();
                            item.code = Convert.ToInt32(reader["cod"].ToString());
                            item.ciTeacher = Convert.ToInt32(reader["ciDocente"].ToString());
                            item.codSubject = Convert.ToInt32(reader["codMateria"].ToString());
                            item.gestion = reader["gestion"].ToString();

                            result.Add(item);
                        }
                    }
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al acceder a los datos", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
    }
}
